using System;
using System.Collections.Generic;
using System.Data.Common;
using Commons.Utils;
using System.Data.SqlClient;
using System.Data;

namespace Commons.Daos
{
	public class DaoSupport : IDisposable
	{
		public PersistenceManager PersistenceManager { get; set; }

		private Dictionary<string, SqlCommand> commands = new Dictionary<string, SqlCommand>();

		protected SqlCommand GetCommand(string sql)
		{
            
			SqlCommand command;
			if (commands.ContainsKey(sql))
			{
				command = commands[sql];
				command.Parameters.Clear();
			}
			else
			{
				command = new SqlCommand(sql, PersistenceManager.GetConnection());
				commands.Add(sql, command);
			}
            
			return command;
		}

		public int QueryForInt(string sql, params SqlParameter[] args)
		{
			return Convert.ToInt32(QueryForObject(sql, args));
		}

		public long QueryForLong(string sql, params SqlParameter[] args)
		{
			return Convert.ToInt64(QueryForObject(sql, args));
		}

		public int? QueryForNullableInt(string sql, params SqlParameter[] args)
		{
			object o = QueryForObject(sql, args);

			return o != null ? (int?) Convert.ToInt32(o) : null;
		}

		public object QueryForObject(string sql, params SqlParameter[] args)
		{
            
			SqlCommand command = GetCommand(sql);
			command.Parameters.AddRange(args);
			return command.ExecuteScalar();
        }

		public T QueryForObject<T>(IRowMapper<T> rowMapper, string sql, params SqlParameter[] args)
		{
            SqlCommand command = GetCommand(sql);
			command.Parameters.AddRange(args);

			using (DbDataReader reader = command.ExecuteReader())
			{
				if (reader.Read())
				{
					return rowMapper.MapRow(reader);
				}
			}
       	    return default(T);
		}

		public List<T> QueryForList<T>(IRowMapper<T> rowMapper, string sql, params SqlParameter[] args)
		{
			List<T> list = new List<T>();
            SqlCommand command = GetCommand(sql);
			command.Parameters.AddRange(args);

			using (DbDataReader reader = command.ExecuteReader())
			{
				while (reader.Read())
				{
					list.Add(rowMapper.MapRow(reader));
				}
			}
            return list;
		}

		public Dictionary<K,T> QueryForDictionary<K,T>(IRowMapper<T> rowMapper, int keyIndex, string sql, params SqlParameter[] args)
		{
			Dictionary<K,T> dictionary = new Dictionary<K,T>();
            SqlCommand command = GetCommand(sql);
			command.Parameters.AddRange(args);

			using (DbDataReader reader = command.ExecuteReader())
			{
				while (reader.Read())
				{
					dictionary.Add((K) reader.GetValue(keyIndex), rowMapper.MapRow(reader));
				}
			}
            return dictionary;
		}

		public HashMultiMap<K,T> QueryForMultiMap<K,T>(IRowMapper<T> rowMapper, int keyIndex, string sql, params SqlParameter[] args)
		{
			HashMultiMap<K,T> multiMap = new HashMultiMap<K,T>();
            SqlCommand command = GetCommand(sql);
			command.Parameters.AddRange(args);

			using (DbDataReader reader = command.ExecuteReader())
			{
				while (reader.Read())
				{
					multiMap.Add((K) reader.GetValue(keyIndex), rowMapper.MapRow(reader));
				}
			}
            return multiMap;
		}

		public int Insert(string sql, params SqlParameter[] args)
		{
            SqlCommand command = GetCommand(sql);
			command.Parameters.AddRange(args);
			//command.ExecuteNonQuery();

			return (int)command.ExecuteScalar();
        }

		public int Update(string sql, params SqlParameter[] args)
		{
            SqlCommand command = GetCommand(sql);
			command.Parameters.AddRange(args);

			return command.ExecuteNonQuery();
       }

		public int Delete(string sql, params SqlParameter[] args)
		{
        	SqlCommand command = GetCommand(sql);
			command.Parameters.AddRange(args);

			return command.ExecuteNonQuery();
        }

		public void Execute(string sql)
		{
        	SqlCommand command = GetCommand(sql);
			command.ExecuteNonQuery();
        }

		public void AggregateExecution(object data, PersistenceCommand command)
		{
			PersistenceManager.AddCommand(data, command);
		}

		public void Dispose()
		{
        	foreach (SqlCommand command in commands.Values)
			{
				command.Dispose();
			}
			commands.Clear();
       }
	}
}
